<?php

namespace Feima\FlightLogProxy;

use PDO as PDO;
/**
 * Created by PhpStorm.
 * User: jimmy
 * Date: 2017/12/7
 * Time: 下午4:17
 */
class DatabaseProxy{

    private $sourceDb;

    private $config;

    /**
     * DatabaseProxy constructor.
     * @param $sourceDb
     */
    public function __construct($config)
    {
        $this->config=$config;

    }



    /**********************        连接管理      ********************/
    //创建连接
    public function getDatabase(){
        if(!$this->sourceDb){
            //$dsn, $username, $passwd, $options
            $dsn=$this->config["dsn"];
            $userName=$this->config["user_name"];
            $password=$this->config["password"];
            $options=array_key_exists("options",$this->config)?$this->config["options"]:[PDO::ATTR_PERSISTENT => true];
            $this->sourceDb=new PDO($dsn, $userName, $password, $options);
        }
        return $this->sourceDb;
    }
    //关闭连接
    public function close(){
        $this->sourceDb=null;
    }

    /**********************        查询     ********************/

    public function fetchColumn($sql){
        $result =$this->getDatabase()->query($sql, PDO::FETCH_ASSOC);
        $record = $result->fetchColumn(0);
        return $record;
    }

    public function fetchOne($sql){
        $result =$this->getDatabase()->query($sql, PDO::FETCH_ASSOC);
        $record = $result->fetch();
        return $record;
    }

    public function fetchAll($sql){
        $result =$this->getDatabase()->query($sql, PDO::FETCH_ASSOC);
        $record = $result->fetchAll();
        return $record;
    }
    //执行查询指令
    public function query($sql){
        $result =$this->getDatabase()->query($sql);
        return $result;
    }

    public function execute($sql){
        $result = $this->getDatabase()->exec($sql);
        return $result;
    }

    public function update($table, $data, $where){
        global $database_name;
        $records =$this->fetchAll("select COLUMN_NAME from information_schema.COLUMNS where table_name = '$table' and table_schema = '$database_name'");
        if(empty($records)){
            throw new Exception("table not exist");
        }

        // 筛选出数据表中存在的字段
        $fields = array();
        foreach ($data as $key => $value) {
            $index = array_search($key, array_column($records, 'COLUMN_NAME'));
            if(is_numeric($index)){
                $fields[$key] = $value;
            }
        }

        // 拼接sql字符串
        $strFields = "";
        foreach ($fields as $key => $value) {
            $strFields .= "$key = :$key,";
        }
        if(empty($strFields)){
            throw new Exception("error fields");
        }

        if(empty($where)){
            throw new Exception("where must not be null");
        }

        $strFields = substr($strFields, 0, -1);
        $sth = $this->getDatabase()->prepare("update $table set $strFields where $where");
        $params = array();
        foreach ($fields as $key => $value) {
            $params[":".$key] = $value;
        }
        // 执行sql
        $sth->execute($params);
        return $sth->rowCount();
    }

    public function begin(){
        $this->getDatabase()->beginTransaction();
    }
    public function commit(){
        $this->getDatabase()->commit();
    }

    public function insert($table, $data){
        // 筛选出数据表中存在的字段
        $fields = array();
        foreach ($data as $key => $value) {
            $fields[$key] = $value;
        }

        // 拼接sql字符串
        $strFields = "";
        $strValues = "";
        foreach ($fields as $key => $value) {
            $strFields .= "$key,";
            $strValues .= ":$key,";
        }
        if(empty($strFields)){
            throw new Exception("error fields");
        }

        $strFields = substr($strFields, 0, -1);
        $strValues = substr($strValues, 0, -1);
        $sth =$this->getDatabase()->prepare("insert into $table($strFields) values($strValues)");

        $params = array();
        foreach ($fields as $key => $value) {
            $params[":".$key] = $value;
        }
        // 执行sql
        $sth->execute($params);
        return true;
    }
}